﻿using Common;
using DAL.DBUtility;
using Enums;
using Enums.Sys;
using Model.CodeManage;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL.CodeManage
{
  public  class GenCodeDAL
    {
        /// <summary>
        /// 获取所有的数据信息
        /// </summary>
        /// <returns></returns>
      public List<GenCodeModel> GetList(string term)
      {
          String sql = @"SELECT cg.ID,cg.CodeType,cg.EnterpriseId,cg.Gen_Code_Nums,oe.code as EnterpriseCode,
                case cg.Status when '1' then '成功' else '未生码' end as Status,oe.EnterpriseName,
                cg.CreateLoginID,cg.CreateLoginName,cg.CreateDate,cg.PatchId,cg.ActiveType,cg.DisableTime,cg.CodeTypeText,
                cg.RankNum,cg.VertialNum,cgg.Batch FROM CodeManage_GenCode cg left join CodeManage_GenCodeBatch cgg
                on cg.PatchId=cgg.ID   left join Organization_Enterprise oe on oe.ID=cg.EnterpriseId WHERE 1=1  AND cg.ViewDel=@ViewDel   and (oe.EnterpriseName like '%" + @term + "%' or cgg.Batch like '%" + @term + "%')";
          SqlParameter[] parmer =  {
                new SqlParameter("@ViewDel",EnumManagement.GetEnumValue(SysEnum.NoDele)),
                 new SqlParameter("@term",term)
           };
          DataTable dt = DbHelperSQL.Query(sql, parmer).Tables[0];
          List<GenCodeModel> modelList = ListDataSet.DataSetToIList<GenCodeModel>(dt).ToList();
          return modelList;
      }
        /// <summary>
        /// 操作
        /// </summary>
        /// <param name="gencodeModel"></param>
        /// <returns></returns>
        public int SaveData(GenCodeModel gencodeModel)
        {
            int result = 0;
            //if (productModel.ID == 0)
            //    result = Insert(productModel);
            //else
            //    result = Edit(productModel);
            return result;
        }

        /// <summary>
        /// 新增生码任务
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ResultEntity Add(GenCodeModel model)
        {
            string sql = @"insert into CodeManage_GenCode(EnterpriseId,CodeType,Gen_Code_Nums,Status,
CreateLoginID,CreateLoginName,CreateDate,ChangeLoginID,ChangeLoginName,ChangeDate,Anti_Fake_Num,Logistics_Num,DisableTime,
ExpiryDate,ViewDel,PatchId,CodeTypeText,ActiveType,VertialNum,RankNum) values(@EnterpriseId,@CodeType,@Gen_Code_Nums,@Status,
@CreateLoginID,@CreateLoginName,@CreateDate,@ChangeLoginID,@ChangeLoginName,@ChangeDate,@Anti_Fake_Num,@Logistics_Num,@DisableTime,@ExpiryDate,@ViewDel,@PatchId,@CodeTypeText,@ActiveType,@VertialNum,@RankNum);select @@IDENTITY; ";

            SqlParameter[] parameters = {
					new SqlParameter("@EnterpriseId",PageValidate.ConvertToTrimInt(model.EnterpriseId)),
                    new SqlParameter("@CodeType",PageValidate.ConvertToTrimString(model.CodeType)),
                  
                    new SqlParameter("@Gen_Code_Nums",PageValidate.ConvertToTrimInt(model.Gen_Code_Nums)),
                    
                    new SqlParameter("@Status", PageValidate.ConvertToTrimInt(model.Status)),
                    new SqlParameter("@CreateLoginID",PageValidate.ConvertToTrimInt(model.CreateLoginID)),
                     new SqlParameter("@CreateLoginName",PageValidate.ConvertToTrimString(model.CreateLoginName)),
                      new SqlParameter("@CreateDate",PageValidate.ConvertToDateTime(model.CreateDate)),
                       new SqlParameter("@ChangeLoginID",PageValidate.ConvertToTrimInt(model.ChangeLoginID)),
                        new SqlParameter("@ChangeLoginName",PageValidate.ConvertToTrimString(model.ChangeLoginName)),
                         new SqlParameter("@ChangeDate",PageValidate.ConvertToDateTime(model.ChangeDate)),
                         new SqlParameter("@Anti_Fake_Num",PageValidate.ConvertToTrimInt(model.Anti_Fake_Num)),
                       new SqlParameter("@Logistics_Num",PageValidate.ConvertToTrimInt(model.Logistics_Num)),
                        new SqlParameter("@DisableTime",PageValidate.ConvertToDateTime(model.DisableTime)),
                       
                         new SqlParameter("@ExpiryDate",PageValidate.ConvertToTrimString(model.ExpiryDate)),
                          
                          new SqlParameter("@ViewDel",PageValidate.ConvertToTrimInt(model.ViewDel)),
                         
                          new SqlParameter("@PatchId",PageValidate.ConvertToTrimInt(model.PatchId)),
                          new SqlParameter("@CodeTypeText",PageValidate.ConvertToTrimString(model.CodeTypeText)),
                        
                           new SqlParameter("@ActiveType",PageValidate.ConvertToTrimInt(model.ActiveType)),
                           new SqlParameter("@VertialNum",PageValidate.ConvertToTrimInt(model.VertialNum)),
                           new SqlParameter("@RankNum",PageValidate.ConvertToTrimInt(model.RankNum))
                           
                           
                         
			};
            return DbHelperSQL.CommonResult(DbHelperSQL.GetSingle(sql, parameters));

        }


        /// <summary>
        /// 修改生码任务
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>

        public ResultEntity Update(GenCodeModel model)
        {
            string sql = @"update CodeManage_GenCode set EnterpriseId=@EnterpriseId,CodeType=@CodeType,Gen_Code_Nums=@Gen_Code_Nums,Status=@Status,ChangeLoginID=@ChangeLoginID,ChangeLoginName=@ChangeLoginName,
ChangeDate=@ChangeDate,Anti_Fake_Num=@Anti_Fake_Num,Logistics_Num=@Logistics_Num,
ExpiryDate=@ExpiryDate,ActiveType=@ActiveType,RankNum=@RankNum,VertialNum=@VertialNum  
                       where ID=@ID;";

            SqlParameter[] parameters = {
                   new SqlParameter("@EnterpriseId",PageValidate.ConvertToTrimInt(model.EnterpriseId)),
                    new SqlParameter("@CodeType",PageValidate.ConvertToTrimInt(model.CodeType)),
                    new SqlParameter("@Gen_Code_Nums",PageValidate.ConvertToTrimInt(model.Gen_Code_Nums)),
                    new SqlParameter("@Status", PageValidate.ConvertToTrimInt(model.Status)),
                    new SqlParameter("@ChangeLoginID",PageValidate.ConvertToTrimInt(model.ChangeLoginID)),
                     new SqlParameter("@ChangeLoginName",PageValidate.ConvertToTrimString(model.ChangeLoginName)),
                     new SqlParameter("@ChangeDate",PageValidate.ConvertToDateTime(model.ChangeDate)),
                      new SqlParameter("@Anti_Fake_Num",PageValidate.ConvertToTrimInt(model.Anti_Fake_Num)),
                    new SqlParameter("@Logistics_Num",PageValidate.ConvertToTrimInt(model.Logistics_Num)),
                     new SqlParameter("@ExpiryDate",PageValidate.ConvertToTrimString(model.ExpiryDate)),
                      new SqlParameter("@ActiveType",PageValidate.ConvertToTrimInt(model.ActiveType)),

                       new SqlParameter("@RankNum",PageValidate.ConvertToTrimInt(model.RankNum)),
                      new SqlParameter("@VertialNum",PageValidate.ConvertToTrimInt(model.VertialNum)),
                 
                    new SqlParameter("@ID",PageValidate.ConvertToTrimInt(model.ID))
			};

            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(sql, parameters));
        }

        /// <summary>
        /// 根据生码批次表id获取生码任务信息
        /// </summary>
        /// <param name="batchId"></param>
        /// <returns></returns>
        public GenCodeModel GetModelByBatch(int batchId)
        {
            GenCodeModel model = new GenCodeModel();
//            string sql = @"select cg.*,cgb.Batch from CodeManage_GenCode cg left join CodeManage_GenCodeBatch cgb
//             on cg.PatchId=cgb.ID where cg.PatchId=@PatchId";
            string sql = @" select cg.*,cgb.Batch,oe.Code  from CodeManage_GenCode cg 
  left join CodeManage_GenCodeBatch cgb
             on cg.PatchId=cgb.ID
             left join Organization_Enterprise oe on cg.EnterpriseId=oe.ID where cg.PatchId=@PatchId";
            SqlParameter[] parmars = { new SqlParameter("@PatchId", PageValidate.ConvertToTrimInt(batchId)) };
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parmars);
            if (sdr.Read())
            {
                model = CommonModelForDown(sdr);
            }
            sdr.Close();
            return model;
        }
        private GenCodeModel CommonModel(SqlDataReader sdr)
        {
            GenCodeModel model = new GenCodeModel();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);
            model.CodeType = PageValidate.ConvertToTrimString(sdr["CodeType"]);
            model.Batch = PageValidate.ConvertToTrimString(sdr["Batch"]);
           
            model.Gen_Code_Nums = PageValidate.ConvertToTrimInt(sdr["Gen_Code_Nums"]);
            model.Image = PageValidate.ConvertToTrimString(sdr["Image"]);
            model.EnterpriseId = PageValidate.ConvertToTrimInt(sdr["EnterpriseId"]);
            model.EnterpriseCode = PageValidate.ConvertToTrimString(sdr["EnterpriseCode"]);
            model.Anti_Fake_Num = PageValidate.ConvertToTrimInt(sdr["Anti_Fake_Num"]);
            model.Logistics_Num = PageValidate.ConvertToTrimInt(sdr["Logistics_Num"]);
            model.DisableTime = PageValidate.ConvertToDateTime(sdr["DisableTime"]);

            model.ExpiryDate = PageValidate.ConvertToTrimString(sdr["ExpiryDate"]);
            model.ActiveType = PageValidate.ConvertToTrimInt(sdr["ActiveType"]);//激活方式
            model.ViewDel = PageValidate.ConvertToTrimInt(sdr["ViewDel"]);
            
            model.CreateLoginID = PageValidate.ConvertToTrimInt(sdr["CreateLoginID"]);
            model.CreateLoginName = PageValidate.ConvertToTrimString(sdr["CreateLoginName"]);
            model.CreateDate = PageValidate.ConvertToDateTime(sdr["CreateDate"]);
            model.ChangeLoginID = PageValidate.ConvertToTrimInt(sdr["ChangeLoginID"]);
            model.ChangeLoginName = PageValidate.ConvertToTrimString(sdr["ChangeLoginName"]);
            model.ChangeDate = PageValidate.ConvertToDateTime(sdr["ChangeDate"]);
            model.RankNum = PageValidate.ConvertToTrimInt(sdr["RankNum"]);
            model.VertialNum = PageValidate.ConvertToTrimInt(sdr["VertialNum"]);
             
            return model;
        }

        private GenCodeModel CommonModelForDown(SqlDataReader sdr)
        {
            GenCodeModel model = new GenCodeModel();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);
            model.CodeType = PageValidate.ConvertToTrimString(sdr["CodeType"]);
            model.Batch = PageValidate.ConvertToTrimString(sdr["Batch"]);

            model.Gen_Code_Nums = PageValidate.ConvertToTrimInt(sdr["Gen_Code_Nums"]);
            model.Image = PageValidate.ConvertToTrimString(sdr["Image"]);
            model.EnterpriseId = PageValidate.ConvertToTrimInt(sdr["EnterpriseId"]);
            model.EnterpriseCode = PageValidate.ConvertToTrimString(sdr["EnterpriseCode"]);
            model.Anti_Fake_Num = PageValidate.ConvertToTrimInt(sdr["Anti_Fake_Num"]);
            model.Logistics_Num = PageValidate.ConvertToTrimInt(sdr["Logistics_Num"]);
            model.DisableTime = PageValidate.ConvertToDateTime(sdr["DisableTime"]);

            model.ExpiryDate = PageValidate.ConvertToTrimString(sdr["ExpiryDate"]);
            model.ActiveType = PageValidate.ConvertToTrimInt(sdr["ActiveType"]);//激活方式
            model.ViewDel = PageValidate.ConvertToTrimInt(sdr["ViewDel"]);

            model.CreateLoginID = PageValidate.ConvertToTrimInt(sdr["CreateLoginID"]);
            model.CreateLoginName = PageValidate.ConvertToTrimString(sdr["CreateLoginName"]);
            model.CreateDate = PageValidate.ConvertToDateTime(sdr["CreateDate"]);
            model.ChangeLoginID = PageValidate.ConvertToTrimInt(sdr["ChangeLoginID"]);
            model.ChangeLoginName = PageValidate.ConvertToTrimString(sdr["ChangeLoginName"]);
            model.ChangeDate = PageValidate.ConvertToDateTime(sdr["ChangeDate"]);
            model.RankNum = PageValidate.ConvertToTrimInt(sdr["RankNum"]);
            model.VertialNum = PageValidate.ConvertToTrimInt(sdr["VertialNum"]);
            model.Code = PageValidate.ConvertToTrimString(sdr["Code"]);
            return model;
        }

        /// <summary>
        /// 获取机构下的最新的批次信息
        /// </summary>
        /// <returns></returns>
        public GenCodeBatch GetMaxBatchByEnterId(int enterId)
        {
            GenCodeBatch model = new GenCodeBatch();
            string sql = @"select top 1 * from CodeManage_GenCodeBatch where EnterpriseId=@EnterpriseId and ViewFlag=@ViewFlag order by ID desc";
            SqlParameter[] parmears = { 
                           new SqlParameter("@EnterpriseId",PageValidate.ConvertToTrimInt(enterId)),
                           new SqlParameter("@ViewFlag",PageValidate.ConvertToTrimInt(EnumManagement.GetEnumValue(SysEnum.Enable)))
                                         };
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parmears);
            if (sdr.Read())
            {
                model = CommonModelForBatch(sdr);
            }
            sdr.Close();
            return model;
        }

        private GenCodeBatch CommonModelForBatch(SqlDataReader sdr)
        {
            GenCodeBatch model = new GenCodeBatch();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);
            model.EnterpriseId = PageValidate.ConvertToTrimInt(sdr["EnterpriseId"]);
          
            model.Batch = PageValidate.ConvertToTrimString(sdr["Batch"]);
            model.ViewFlag = PageValidate.ConvertToTrimInt(sdr["ViewFlag"]);
           
            model.CreateLoginID = PageValidate.ConvertToTrimInt(sdr["CreateLoginID"]);
            model.CreateLoginName = PageValidate.ConvertToTrimString(sdr["CreateLoginName"]);
            model.CreateDate = PageValidate.ConvertToDateTime(sdr["CreateDate"]);
            model.ChangeLoginID = PageValidate.ConvertToTrimInt(sdr["ChangeLoginID"]);
            model.ChangeLoginName = PageValidate.ConvertToTrimString(sdr["ChangeLoginName"]);
            model.ChangeDate = PageValidate.ConvertToDateTime(sdr["ChangeDate"]);

            return model;
        }


        /// <summary>
        /// 根据主键编号查询对象
        /// </summary>
        /// <returns></returns>
        public GenCodeModel GetModel(int id)
        {
            GenCodeModel model = new GenCodeModel();
            string sql = @"select cg.*,cgb.Batch from CodeManage_GenCode cg left join CodeManage_GenCodeBatch cgb
             on cg.PatchId=cgb.ID where cg.ID=@ID ";
            SqlParameter[] parmars ={
                    new SqlParameter("@ID",PageValidate.ConvertToTrimInt(id))
               };
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parmars);
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }
        
    }
}
